﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entity;
using System.Data.SqlClient;
using DbCommon;
using System.Data;

namespace DAL
{
    public class LocalDAL
    {
        DbHelper dbhelper = new DbHelper();
        /// <summary>
        /// 添加一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Insert(BoLocal model)
        {
            model.LOC_ID = Guid.NewGuid().ToString();
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("INSERT INTO [Local] ");
            sql.Append("([LOC_ID],[LOC_NAME],[LOC_CODE],[LOC_IsShow]) ");
            sql.Append("VALUES ");
            sql.Append("(@LOC_ID,@LOC_NAME,@LOC_CODE,@LOC_IsShow)");
            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 修改一条记录
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        public int Update(BoLocal model)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Local] SET ");
            sql.Append("LOC_NAME=@LOC_NAME,LOC_CODE=@LOC_CODE,LOC_IsShow=@LOC_IsShow");
            sql.Append(" WHERE ");
            sql.Append("LOC_ID=@LOC_ID");
            return dbhelper.ExcuteNoQuery(sql.ToString(), GetParameters(model));
        }

        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <returns></returns>
        public int Delete(string aCA_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("UPDATE [Local] SET ");
            sql.Append("LOC_IsShow='false'");
            sql.Append(" WHERE ");
            sql.Append("LOC_ID=@LOC_ID");
            SqlParameter[] parameter = new SqlParameter[1];
            parameter[0] = new SqlParameter("@LOC_ID", SqlDbType.NVarChar, 50);
            parameter[0].Value = aCA_ID;
            return dbhelper.ExcuteNoQuery(sql.ToString(), parameter);
        }
        public BoLocal GetModel(string aCA_ID)
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT * ");
            sql.Append("FROM Local  ");
            sql.Append(" WHERE ");
            sql.Append("LOC_ID=@LOC_ID AND LOC_IsShow='true'");
            SqlParameter[] pr = new SqlParameter[]
            {
                new SqlParameter("@LOC_ID",SqlDbType.NVarChar, 50)
            };
            pr[0].Value = aCA_ID;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(), pr);
            IList<BoLocal> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }
        //取得所有学院的信息 不包括假删除的
        public List<BoLocal> GetLocal()
        {
            System.Text.StringBuilder sql = new System.Text.StringBuilder();
            sql.Append("SELECT * ");
            sql.Append("FROM Local");
            sql.Append(" WHERE ");
            sql.Append("LOC_IsShow='true'");
            DataTable table = dbhelper.ExcuteQuery(sql.ToString());
            List<BoLocal> list = GetList(table);
            return list != null && list.Count > 0 ? list : null;
        }

          /// <summary>
        /// 通过校区名字来获得校区对象
        /// </summary>
        /// <param name="LocalName">校区的名字</param>
        /// <returns>返回校区对象</returns>
        public BoLocal GetLocalByName(string LocalName)
        {
            string sql = "select * from Local where LOC_IsShow='true' and LOC_NAME=@LOC_NAME";
            SqlParameter[] parameter = new SqlParameter[]
            {
                new SqlParameter("@LOC_NAME",LocalName),
            };
            parameter[0].Value = LocalName;
            DataTable table = dbhelper.ExcuteQuery(sql.ToString(),parameter);
            List<BoLocal> list = GetList(table);
            return list != null && list.Count > 0 ? list[0] : null;
        }


        //  /// <summary>
        ///// 通过对象来查询重复的个数
        ///// </summary>
        ///// <param name="model"></param>
        ///// <returns>返回重复的个数</returns>
        //public int GetRepeat(BoLocal model)
        //{
        //    string sql = "select count(*) from Local where LOC_IsShow='true' and LOC_NAME=@LOC_NAME ";
        //}
        public List<BoLocal> GetList(DataTable table)
        {
            List<BoLocal> list = new List<BoLocal>();
            if (table != null && table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    BoLocal model = new BoLocal();
                    model.LOC_ID = (string)table.Rows[i]["LOC_ID"];
                    model.LOC_IsShow = table.Rows[i]["LOC_IsShow"] == System.DBNull.Value ? true : (bool)table.Rows[i]["LOC_IsShow"];

                    model.LOC_CODE = table.Rows[i]["LOC_CODE"] == System.DBNull.Value ? "" : (string)table.Rows[i]["LOC_CODE"];
                    model.LOC_NAME = table.Rows[i]["LOC_NAME"] == System.DBNull.Value ? "" : (string)table.Rows[i]["LOC_NAME"];
                    list.Add(model);
                }
            }
            return list;
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="model">实体类</param>
        /// <returns></returns>
        private SqlParameter[] GetParameters(BoLocal model)
        {
            SqlParameter[] parameters = new SqlParameter[] {
                new SqlParameter("@LOC_ID",SqlDbType.NVarChar,50),                
                new SqlParameter("@LOC_IsShow",SqlDbType.Bit,2),                
                new SqlParameter("@LOC_CODE",SqlDbType.NVarChar,50),
                new SqlParameter("@LOC_NAME",SqlDbType.NVarChar,50)
            };
            parameters[0].Value = model.LOC_ID;
            parameters[1].Value = model.LOC_IsShow;
            parameters[2].Value = model.LOC_CODE;
            parameters[3].Value = model.LOC_NAME;
            
            return parameters;
        }

    }
}
